package org.csu.myPetStore.persistence.impl;

import org.csu.myPetStore.domain.Account;
import org.csu.myPetStore.domain.CloseSource;
import org.csu.myPetStore.persistence.AccountDAO;
import org.csu.myPetStore.persistence.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class AccountDAOImpl implements AccountDAO {

    private static final String GET_ACCOUNT_BY_USERNAME = "SELECT SIGNON.USERNAME,ACCOUNT.EMAIL,ACCOUNT.FIRSTNAME, ACCOUNT.LASTNAME,ACCOUNT.STATUS,ACCOUNT.ADDR1 AS address1,ACCOUNT.ADDR2 AS address2,ACCOUNT.CITY,ACCOUNT.STATE,ACCOUNT.ZIP,ACCOUNT.COUNTRY,ACCOUNT.PHONE,PROFILE.LANGPREF AS languagePreference,PROFILE.FAVCATEGORY AS favouriteCategoryId,PROFILE.MYLISTOPT AS listOption,PROFILE.BANNEROPT AS bannerOption,BANNERDATA.BANNERNAME FROM ACCOUNT, PROFILE, SIGNON, BANNERDATA WHERE ACCOUNT.USERID = ? AND SIGNON.USERNAME = ACCOUNT.USERID AND PROFILE.USERID = ACCOUNT.USERID AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY";

    private static final String GET_ACCOUNT_BY_USERNAME_AND_PASSWORD = "SELECT SIGNON.USERNAME,ACCOUNT.EMAIL,ACCOUNT.FIRSTNAME,ACCOUNT.LASTNAME,ACCOUNT.STATUS,ACCOUNT.ADDR1 AS address1,ACCOUNT.ADDR2 AS address2,ACCOUNT.CITY,ACCOUNT.STATE,ACCOUNT.ZIP,ACCOUNT.COUNTRY, ACCOUNT.PHONE,PROFILE.LANGPREF AS languagePreference,PROFILE.FAVCATEGORY AS favouriteCategoryId,PROFILE.MYLISTOPT AS listOption,PROFILE.BANNEROPT AS bannerOption,BANNERDATA.BANNERNAME FROM ACCOUNT, PROFILE, SIGNON, BANNERDATA WHERE ACCOUNT.USERID = ? AND SIGNON.PASSWORD = ? AND SIGNON.USERNAME = ACCOUNT.USERID AND PROFILE.USERID = ACCOUNT.USERID AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY";

    private static final String UPDATE_ACCOUNT = "UPDATE ACCOUNT SET EMAIL = ?, FIRSTNAME = ?, LASTNAME = ?,STATUS = ?,ADDR1 = ?,ADDR2 = ?,CITY = ?,STATE = ?,ZIP = ?,COUNTRY = ?,PHONE = ? WHERE USERID = ?";

    private static final String INSERT_ACCOUNT = " INSERT INTO ACCOUNT (EMAIL, FIRSTNAME, LASTNAME, STATUS, ADDR1, ADDR2, CITY, STATE, ZIP, COUNTRY, PHONE, USERID) VALUES(?, ?, ?, ?, ?,  ?, ?, ?, ?, ?, ?, ?)";

    private static final String UPDATE_PROFILE = " UPDATE PROFILE SET LANGPREF = ?,FAVCATEGORY = ? WHERE USERID = ?";

    private static final String INSERT_PROFILE = " INSERT INTO PROFILE (LANGPREF, FAVCATEGORY, USERID) VALUES (?, ?, ?)";

    private static final String UPDATE_SIGNON = "  UPDATE SIGNON SET PASSWORD = ? WHERE USERNAME = ?";

    private static final String INSERT_SIGNON = "  INSERT INTO SIGNON (PASSWORD,USERNAME) VALUES (?, ?)";

    private static final String FIND_ACCOUNT = "  SELECT * FROM ACCOUNT WHERE USERID = ?";

    @Override
    public Account getAccountByUsername(String username) {
        Account account = null;

        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_ACCOUNT_BY_USERNAME);
            preparedStatement.setString(1,username);

            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                account = new Account();
                account.setUsername(resultSet.getString(1));
                account.setEmail(resultSet.getString(2));
                account.setFirstName(resultSet.getString(3));
                account.setLastName(resultSet.getString(4));
                account.setStatus(resultSet.getString(5));
                account.setAddress1(resultSet.getString(6));
                account.setAddress2(resultSet.getString(7));
                account.setCity(resultSet.getString(8));
                account.setZip(resultSet.getString(9));
                account.setCountry(resultSet.getString(10));
                account.setPhone(resultSet.getString(11));
                account.setLanguagePreference(resultSet.getString(12));
                account.setFavouriteCategoryId(resultSet.getString(13));

                //ToDo
                account.setListOption(resultSet.getBoolean(14));
                account.setBannerOption(resultSet.getBoolean(15));
                account.setBannerName(resultSet.getString(16));
            }

            CloseSource closeSource = new CloseSource();
            closeSource.closeSource(connection,preparedStatement,resultSet,null);

        }catch (Exception e){
            e.printStackTrace();
        }


        return account;
    }

    @Override
    public Account getAccountByUsernameAndPassword(Account account){

        Account account1 = null;
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_ACCOUNT_BY_USERNAME_AND_PASSWORD);
            preparedStatement.setString(1,account.getUsername());
            preparedStatement.setString(2,account.getPassword());

            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                account1 = new Account();
                account1.setUsername(resultSet.getString(1));
                account1.setEmail(resultSet.getString(2));
                account1.setFirstName(resultSet.getString(3));
                account1.setLastName(resultSet.getString(4));
                account1.setStatus(resultSet.getString(5));
                account1.setAddress1(resultSet.getString(6));
                account1.setAddress2(resultSet.getString(7));
                account1.setCity(resultSet.getString(8));
                account1.setZip(resultSet.getString(9));
                account1.setCountry(resultSet.getString(10));
                account1.setPhone(resultSet.getString(11));
                account1.setLanguagePreference(resultSet.getString(12));
                account1.setFavouriteCategoryId(resultSet.getString(13));
                //ToDo
                account1.setListOption(resultSet.getBoolean(14));
                account1.setBannerOption(resultSet.getBoolean(15));
                account1.setBannerName(resultSet.getString(16));
            }

            CloseSource closeSource = new CloseSource();
            closeSource.closeSource(connection,preparedStatement,resultSet,null);

        }catch (Exception e){
            e.printStackTrace();
        }
        return  account1;
    }

    @Override
    public void insertAccount(Account account) {

        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_ACCOUNT);
            preparedStatement.setString(1,account.getEmail());
            preparedStatement.setString(2,account.getFirstName());
            preparedStatement.setString(3,account.getLastName());
            preparedStatement.setString(4,account.getStatus());
            preparedStatement.setString(5,account.getAddress1());
            preparedStatement.setString(6,account.getAddress2());
            preparedStatement.setString(7,account.getCity());
            preparedStatement.setString(8,account.getState());
            preparedStatement.setString(9,account.getZip());
            preparedStatement.setString(10,account.getCountry());
            preparedStatement.setString(11,account.getPhone());
            preparedStatement.setString(12,account.getUsername());

            int result = preparedStatement.executeUpdate();

            if(result == 1)
                System.out.println("注册成功");

            CloseSource closeSource = new CloseSource();
            closeSource.closeSource(connection,preparedStatement,null,null);

        }catch (Exception e){
            e.printStackTrace();
        }

    }

    @Override
    public void insertProfile(Account account) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_PROFILE);
            preparedStatement.setString(1,account.getLanguagePreference());
            preparedStatement.setString(2,account.getFavouriteCategoryId());
            preparedStatement.setString(3,account.getUsername());

            int result = preparedStatement.executeUpdate();

            if(result == 1)
                System.out.println("插入用户成功");

            CloseSource closeSource = new CloseSource();
            closeSource.closeSource(connection,preparedStatement,null,null);

        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void insertSignon(Account account) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_SIGNON);
            preparedStatement.setString(1,account.getPassword());
            preparedStatement.setString(2,account.getUsername());


            int result = preparedStatement.executeUpdate();

            if(result == 1)
                System.out.println("插入用户成功");

            CloseSource closeSource = new CloseSource();
            closeSource.closeSource(connection,preparedStatement,null,null);

        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void updateAccount(Account account) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_ACCOUNT);
            preparedStatement.setString(1,account.getEmail());
            preparedStatement.setString(2,account.getFirstName());
            preparedStatement.setString(3,account.getLastName());
            preparedStatement.setString(4,account.getStatus());
            preparedStatement.setString(5,account.getAddress1());
            preparedStatement.setString(6,account.getAddress2());
            preparedStatement.setString(7,account.getCity());
            preparedStatement.setString(8,account.getState());
            preparedStatement.setString(9,account.getZip());
            preparedStatement.setString(10,account.getCountry());
            preparedStatement.setString(11,account.getPhone());
            preparedStatement.setString(12,account.getUsername());

            int result = preparedStatement.executeUpdate();
            if(result == 1)
                System.out.println("更新成功");

            CloseSource closeSource = new CloseSource();
            closeSource.closeSource(connection,preparedStatement,null,null);

        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void updateProfile(Account account) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_PROFILE);
            preparedStatement.setString(1,account.getLanguagePreference());
            preparedStatement.setString(2,account.getFavouriteCategoryId());
            preparedStatement.setString(3,account.getUsername());

            int result = preparedStatement.executeUpdate();
            if(result == 1)
                System.out.println("更新成功");

            CloseSource closeSource = new CloseSource();
            closeSource.closeSource(connection,preparedStatement,null,null);

        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void updateSignon(Account account) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_SIGNON);
            preparedStatement.setString(1,account.getPassword());
            preparedStatement.setString(2,account.getUsername());


            int result = preparedStatement.executeUpdate();
            if(result == 1)
                System.out.println("更新成功");

            CloseSource closeSource = new CloseSource();
            closeSource.closeSource(connection,preparedStatement,null,null);

        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public boolean isUserNameExist(String username){
        boolean exist = false;
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(FIND_ACCOUNT);
            preparedStatement.setString(1,username);

            ResultSet resultSet = preparedStatement.executeQuery();

            if(resultSet.next()) {
                System.out.println("用户名已存在，请更换");
                exist = true;
            }else{
                System.out.println("用户名可使用");
            }

            CloseSource closeSource = new CloseSource();
            closeSource.closeSource(connection,preparedStatement,resultSet,null);

        }catch (Exception e){
            e.printStackTrace();
        }

        if(exist){
            return true;
        }else{
            return false;
        }

    }
    //用户注册时判断用户名是否已经存在

}
